﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;

namespace Wicresoft.UnifyShow.BusinessImpl
{
    public class SQLHelper
    {

        //完全可以不用Join 可以通过视图在数据库实现，性能还更好。

        #region common methods
        ///// <summary>  
        /////  生成最终的sql语句
        ///// </summary>  
        ///// <param name="type">标明是select,update,insert还是delete</param>
        ///// <param name="dtName">操作的表名</param>
        ///// <param name="param">传进来的参数，列名+值+列的数据类型， 另外还包含了操作动作，是update的set，还是where</param>
        ///// <param name="orderBy">需要排序的信息，包含列+排序类型</param>
        ///// <param name="ps">ps和pi是用来分页的，为空则不分页，ps为pageSize缩写</param>
        ///// <param name="pi">ps和pi是用来分页的，为空则不分页，pi为pageIndex缩写</param>
        ///// <returns>sql语句</returns>  
        internal static string PrepareSqlStr(DBMType type, string tableName, IList<QueryParameter> param, IList<OrderbyParameter> orderBy, Nullable<int> ps, Nullable<int> pi, JoinCauses joins = null)
        {
            string sqlStr = string.Empty;
            string setSqlStr = string.Empty;
            string whereSqlStr = string.Empty;
            string orderbySqlStr = string.Empty;
            string joinSqlStr = string.Empty;
            string tableNameAfter = tableName.Replace("\"", "").Replace(".", "").Substring(0, 4);
            if (param != null)
            {
                setSqlStr = PrepareSqlSetStr(param, tableNameAfter);
                whereSqlStr = PrepareSqlWhereStr(param, tableNameAfter);
            }
            if (orderBy != null)
            {
                orderbySqlStr = PrepareSqlOrderByStr(orderBy);
            }

            if (joins != null)
            {
                joinSqlStr = PrepareSqlJoinCauseStr(joins);
            }


            if (type == DBMType.select && ps.HasValue != false && pi.HasValue != false)
            {
                int beginIndex = (pi.Value - 1) * ps.Value + 1;
                int endIndex = beginIndex + ps.Value - 1;
                if (string.IsNullOrEmpty(orderbySqlStr))
                {
                    if (string.IsNullOrWhiteSpace(joinSqlStr))
                    {
                        sqlStr = string.Concat("select * from (select t.*,rownum rn from ", tableName, " t ", whereSqlStr, " )  where rn between ",
                            beginIndex, " and ", endIndex);
                    }
                    else
                    {
                        sqlStr = string.Concat("select * from (select t.*,rownum rn from (select * from ", tableName, " z ", joinSqlStr, ") t ",
                            whereSqlStr, ")  where rn between ", beginIndex, " and ", endIndex);
                    }
                }
                else
                {
                    if (string.IsNullOrWhiteSpace(joinSqlStr))
                    {
                        sqlStr = string.Concat("select * from (select t.*,rownum rn from (select * from ", tableName, " z" + " ", whereSqlStr, " ", orderbySqlStr, ") t ",
                            " )where rn between ", beginIndex, " and ", endIndex);
                    }
                    else
                    {
                        sqlStr = string.Concat("select * from (select t.*,rownum rn from (select * from ", tableName,
                            " z ", joinSqlStr, " ", whereSqlStr, " ", orderbySqlStr, ") t )  where rn between ", beginIndex, " and ", endIndex);
                    }
                }
            }
            else
            {
                switch (type)
                {
                    case DBMType.select:
                        sqlStr = string.Concat("select * from ", tableName, " z ", joinSqlStr, whereSqlStr, orderbySqlStr);
                        break;
                    case DBMType.count:
                        sqlStr = string.Concat("select count(*) from ", tableName, " z ", joinSqlStr, whereSqlStr, orderbySqlStr);
                        break;
                    case DBMType.update:
                        sqlStr = string.Concat("update ", tableName, setSqlStr, joinSqlStr, whereSqlStr);
                        break;
                    case DBMType.insert:
                        string colStr = string.Empty;
                        string valStr = string.Empty;
                        PrepareSqlInsertStr(param, out colStr, out valStr, tableNameAfter);
                        sqlStr = string.Concat("insert into ", tableName, " (", colStr, ") values (", valStr, ")");
                        break;
                    case DBMType.delete:
                        sqlStr = string.Concat("delete from ", tableName, joinSqlStr, whereSqlStr);
                        break;
                    default:
                        throw new NotImplementedException(string.Concat("Database maintenance type ", Enum.GetName(typeof(DBMType), type), " is not implemented"));
                }
            }

            return sqlStr;
        }

        private static string PrepareSqlJoinCauseStr(JoinCauses joins)
        {
            string connectString = " and ";
            string result = string.Empty;
            if (joins != null)
            {
                List<JoinCause> list = joins.Get();

                foreach (JoinCause item in list)
                {

                    result += string.Concat(GetJoinTypeString(item.Type), "(", SQLHelper.PrepareSqlStr(DBMType.select, item.TableName, item.GetQueryParameter().ToArray(),
                        null, null, null), ") ", item.Alia, " on");
                    foreach (OnQueryParameter ons in item.GetOnQuery())
                    {
                        result = result + ons.ToString() + connectString;
                    }

                    result = result.Substring(0, result.Length - connectString.Length);
                }
            }
            return result;
        }

        private static string GetJoinTypeString(JoinType type)
        {
            string result = string.Empty;
            switch (type)
            {
                case JoinType.inner:
                    result = " inner join ";
                    break;
                case JoinType.left:
                    result = " left join ";
                    break;
                case JoinType.right:
                    result = " right join ";
                    break;
                case JoinType.full:
                    result = " full join ";
                    break;
                default:
                    throw new InvalidExpressionException("invalid Join type");
            }
            return result;
        }

        ///// <summary>  
        /////  生成sql语句orderby部分
        ///// </summary>
        ///// <param name="orderBy">需要排序的信息，包含列+排序类型</param>
        ///// <returns>sql语句orderby部分</returns>  
        private static string PrepareSqlOrderByStr(IList<OrderbyParameter> orderBy)
        {
            string orderByStr = string.Empty;
            if (orderBy != null)
            {
                bool mark = true;
                foreach (var item in orderBy)
                {
                    if (!string.IsNullOrEmpty(item.orderByCol))
                    {
                        if (mark)
                        {
                            orderByStr = " order by ";
                            mark = false;
                        }
                        string order = GetOrderby(item.orderByType);
                        orderByStr = string.Concat(orderByStr, item.orderByCol, " ", order, ",");
                    }
                }
            }
            return orderByStr == string.Empty ? string.Empty : orderByStr.Substring(0, orderByStr.Length - 1);
        }
        ///// <summary>  
        /////  生成sql语句where
        ///// </summary>
        ///// <param name="queryParameters">传入的参数数组，包含列名+值+列的数据类型</param>
        ///// <returns>sql语句where部分</returns>  
        private static string PrepareSqlWhereStr(IList<QueryParameter> queryParameters, string tableName)
        {
            string str = string.Empty;
            string linkword = " and ";
            if (queryParameters != null)
            {
                bool mark = true;
                foreach (var item in queryParameters)
                {
                    if (!string.IsNullOrEmpty(item.OracleDbColumnName) && item.PredicateType == PredicateType.where)
                    {
                        if (mark)
                        {
                            str = " where ";
                            mark = false;
                        }
                        if (item.QueryOperator == QueryOperatorType.none)
                            throw new InvalidOperationException("The Queryparameter need the QueryOperatorType property in the where cause");
                        string opr = GetOperator(item.QueryOperator);
                        if (item.QueryOperator == QueryOperatorType.isNotNull || item.QueryOperator == QueryOperatorType.isNull)
                        {
                            str = string.Concat(str, item.OracleDbColumnName, opr, linkword);
                        }
                        else
                        {
                            str = string.Concat(str, item.OracleDbColumnName, opr, ":", item.OracleDbColumnName,
                                Enum.GetName(typeof(PredicateType), PredicateType.where), (int)item.QueryOperator, tableName, linkword);
                        }
                    }
                }
            }
            return str == string.Empty ? string.Empty : str.Substring(0, str.Length - linkword.Length);
        }

        private static string PrepareSqlSetStr(IList<QueryParameter> queryParameters, string tableName)
        {
            string str = string.Empty;
            string linkword = ",";
            if (queryParameters != null)
            {
                bool mark = true;
                foreach (var item in queryParameters)
                {
                    if (!string.IsNullOrEmpty(item.OracleDbColumnName) && item.PredicateType == PredicateType.set)
                    {
                        if (mark)
                        {
                            str = " set ";
                            mark = false;
                        }
                        if (item.QueryOperator == QueryOperatorType.equal || item.QueryOperator == QueryOperatorType.none)
                        {
                            str = string.Concat(str, item.OracleDbColumnName, "=", ":", item.OracleDbColumnName, Enum.GetName(typeof(PredicateType), PredicateType.set), tableName, linkword);
                        }
                        else
                        {
                            throw new InvalidOperationException("Invalid Opertaion In Update");
                        }
                    }
                }
            }
            return str == string.Empty ? string.Empty : str.Substring(0, str.Length - linkword.Length);
        }

        private static void PrepareSqlInsertStr(IList<QueryParameter> param, out string colNameStr, out string valueStr, string tableName)
        {
            colNameStr = string.Empty;
            valueStr = string.Empty;
            foreach (QueryParameter p in param)
            {
                if (p.PredicateType != PredicateType.insert) continue;
                if (string.IsNullOrEmpty(colNameStr) != false)
                {
                    colNameStr = p.OracleDbColumnName;
                }
                else
                {
                    colNameStr = string.Concat(colNameStr, ",", p.OracleDbColumnName);
                }
                if (string.IsNullOrEmpty(valueStr) != false)
                {
                    valueStr = string.Concat(":", p.OracleDbColumnName, Enum.GetName(typeof(PredicateType), PredicateType.insert), tableName);
                }
                else
                {
                    valueStr = string.Concat(valueStr, ",:", p.OracleDbColumnName, Enum.GetName(typeof(PredicateType), PredicateType.insert), tableName);
                }
            }
        }

        private static string GetOperator(QueryOperatorType type)
        {
            string str = string.Empty;
            switch (type)
            {
                case QueryOperatorType.equal:
                    str = "=";
                    break;
                case QueryOperatorType.notEqual:
                    str = "<>";
                    break;
                case QueryOperatorType.like:
                    str = " like ";
                    break;
                case QueryOperatorType.bigger:
                    str = ">=";
                    break;
                case QueryOperatorType.biggerNotEqual:
                    str = ">";
                    break;
                case QueryOperatorType.smaller:
                    str = "<=";
                    break;
                case QueryOperatorType.smallerNotEqual:
                    str = "<";
                    break;
                case QueryOperatorType.isNull:
                    str = " is null";
                    break;
                case QueryOperatorType.isNotNull:
                    str = " is not null";
                    break;
                case QueryOperatorType.contains:
                    str = " in ";
                    break;
                default:
                    throw new Exception("Undefined operator");
            }

            return str;
        }
        private static string GetOrderby(OrderByType type)
        {
            string str = string.Empty;
            switch (type)
            {
                case OrderByType.ascending:
                default:
                    str = "asc";
                    break;
                case OrderByType.descending:
                    str = "desc";
                    break;
            }

            return str;
        }

        internal static List<OracleParameter> PrepareOracleParameter(IList<QueryParameter> queryParameters, string tableName)
        {
            string tableNameAfter = tableName.Replace("\"", "").Replace(".", "").Substring(0, 4);
            if (queryParameters != null)
            {
                List<OracleParameter> commandParameters = new List<OracleParameter>();

                foreach (var item in queryParameters)
                {
                    if (!string.IsNullOrEmpty(item.OracleDbColumnName) && item.QueryOperator != QueryOperatorType.isNotNull
                       && item.QueryOperator != QueryOperatorType.isNull)
                    {
                        OracleParameter oracleParameter;
                        if (item.PredicateType == PredicateType.where)
                        {
                            oracleParameter = new OracleParameter(string.Concat(":", item.OracleDbColumnName,
   Enum.GetName(typeof(PredicateType), item.PredicateType), (int)item.QueryOperator, tableNameAfter), item.OracleDbColumnType, ParameterDirection.Input);
                        }
                        else
                        {
                            oracleParameter = new OracleParameter(string.Concat(":", item.OracleDbColumnName,
   Enum.GetName(typeof(PredicateType), item.PredicateType), tableNameAfter), item.OracleDbColumnType, ParameterDirection.Input);
                        }


                        oracleParameter.Value = item.OracleDbColumnValue;
                        if (item.QueryOperator == QueryOperatorType.like)
                        {
                            oracleParameter.Value = "%" + item.OracleDbColumnValue + "%";
                        }
                        commandParameters.Add(oracleParameter);
                    }
                }
                return commandParameters;
            }
            else
            {
                return null;
            }
        }
        #endregion

        internal static OrderByType GetSortDirection(string sortDirection)
        {
            if (sortDirection != null)
            {
                if (sortDirection.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    sortDirection.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    return OrderByType.descending;
                }
            }
            return OrderByType.ascending;
        }

        internal static string GetOracleSortString(string sortDirection)
        {
            if (sortDirection != null)
            {
                if (sortDirection.Equals("DESC", StringComparison.OrdinalIgnoreCase) ||
                    sortDirection.Equals("DESCENDING", StringComparison.OrdinalIgnoreCase))
                {
                    return " desc ";
                }
            }
            return " asc ";
        }

        internal static string GetOracleSortStringContainsName(string columnName, string sortDirection)
        {
            return " order by " + columnName + " " + GetOracleSortString(sortDirection);
        }

        internal static string GetPageSQLFormDirectSQL(string sql, string columnName, string direction, int page, int pageIndex)
        {
            int beginIndex, endIndex;
            beginIndex = (pageIndex - 1) * page + 1;
            endIndex = beginIndex + page - 1;
            return string.Concat("select * from (select t.*,rownum rn from ", " ( ", sql, GetOracleSortStringContainsName(columnName, direction), " ) t ",
                " )  where rn between ", beginIndex, " and ", endIndex);
        }

        internal static string GetCountSQLFormDirectSQL(string sql)
        {
            return string.Concat("select count(*) from ", " ( ", sql, " ) t ");
        }

        internal static OrderbyParameter GetOrderbyParameterByColumnNameAndSort(string columnNam, string sort)
        {
            OrderbyParameter op = new OrderbyParameter();
            op.orderByCol = columnNam;
            op.orderByType = SQLHelper.GetSortDirection(sort);
            return op;
        }

        public static void ExcuteStoreProceduer(string procedureName, string connectionString)
        {
            using (var con = new OracleConnection(connectionString))
            {
                OracleCommand comm = new OracleCommand();

                comm.CommandText = procedureName;
                comm.CommandType = CommandType.StoredProcedure;

                try
                {
                    con.Open();
                    comm.ExecuteNonQuery();
                }
                catch (Exception)
                {
                    throw;
                }
                finally
                {
                    con.Close();
                }
            }
        }
    }
}
